<?php

/**
 * Menashop
 *
 * LICENSE
 *
 * This source file is subject to the license that is bundled
 * with this project in the file LICENSE.txt
 *
 * @category   Zend
 * @package    Storefront_Model
 * @subpackage Product
 * @copyright  Copyright (c) 2011 Menashop UA Inc. (http://www.menashop.com.ua)
 * @license    LICENSE.txt
 * @version    1.0.0
 * @since      File available since Release 1.0.0
 */

/**
 * The model for product
 * 
 * @category   Zend
 * @package    Storefront_Model
 * @subpackage Product
 * @copyright  Copyright (c) 2011 Menashop UA Inc. (http://www.menashop.com.ua)
 * @license    LICENSE.txt
 */
class Storefront_Model_Product extends Zend_Db_Table_Abstract
{

    /**
     * The name of table
     * @var string 
     */
    protected $_name = 'product';

    /**
     * Get product by ID and language
     * @param integer $id
     * @param integer $languageId
     * @return object 
     */
    public function getByIdAndLanguage($id, $languageId)
    {
        $query = $this->getAdapter()->select();
        $query->from(array('p' => $this->_name), array('p.id', 'p.mpn', 'p.name as name', 'p.low_pic_url', 'p.price'));
        $query->join(array('c' => 'category'), 'c.id = p.category_id', 'c.id as category_id');
        $query->join(array('ct' => 'category_translation'), 'c.id = ct.category_id', 'ct.name AS category_name');
        $query->join(array('m' => 'manufacturer'), 'm.id = p.manufacturer_id', 'm.name AS manufacturer_name');
        $query->where('p.id = ?', $id);
        $query->where('ct.language_id = ?', $languageId);
        $result = $this->getAdapter()->fetchRow($query, null, Zend_Db::FETCH_OBJ);
        if ($result) {
            $result->category_name = mb_strtoupper(mb_substr($result->category_name, 0, 1, 'utf8'), 'utf8') . mb_substr($result->category_name, 1, 255, 'utf8');
        }
        return $result;
    }

    /**
     * Get products by category and language
     * @param integer $categoryId
     *      ID of category
     * @param integer $languageId
     *      ID of language
     * @param integer $page
     *      current page 
     * @param array $rowCount
     *      per page
     * @return array 
     */
    public function getByCategoryAndLanguage($categoryId, $languageId, $order, $direction, $page, $rowCount)
    {
        $query = $this->getAdapter()->select();
        $query->from(array('p' => $this->_name), array('p.id', 'p.mpn', 'p.name AS name', 'p.thumb_pic_url', 'p.price'));
        $query->join(array('c' => 'category'), 'c.id = p.category_id', '');
        $query->join(array('m' => 'manufacturer'), 'm.id = p.manufacturer_id', 'm.name AS manufacturer_name');
        $query->joinLeft(array('pt' => 'product_translation'), 'p.id = pt.product_id', array('pt.long_summary_description'));
        $query->where('c.id = ?', $categoryId);
        $query->where('pt.language_id IS NULL OR pt.language_id = ?', $languageId);
        $query->order($order . ' ' . $direction);
        $query->limitPage($page, $rowCount);
        $result = $this->getAdapter()->fetchAll($query, null, Zend_Db::FETCH_OBJ);
        // read data about products from result
        foreach ($result as $row) {
            $row->long_summary_description = substr($row->long_summary_description, strpos($row->long_summary_description, '.') + 1);
        }
        return $result;
    }

    /**
     * Get products by category and language
     * @param integer $categoryId
     * @param integer $languageId
     * @return array 
     */
    public function getCountByCategoryAndLanguage($categoryId, $languageId)
    {
        // get entries for page
        $query = $this->getAdapter()->select();
        $query->from(array('p' => $this->_name), new Zend_Db_Expr('count(*)'));
        $query->join(array('c' => 'category'), 'c.id = p.category_id', '');
        $query->join(array('m' => 'manufacturer'), 'm.id = p.manufacturer_id', '');
        $query->joinLeft(array('pt' => 'product_translation'), 'p.id = pt.product_id', '');
        $query->where('c.id = ?', $categoryId);
        $query->where('pt.language_id IS NULL OR pt.language_id = ?', $languageId);
        $result = $this->getAdapter()->fetchOne($query);
        return $result;
    }

    /**
     * Find product IDs by mpn
     * @param string $mpn 
     * @return array
     */
    public function findIdsByMpn($mpn)
    {
        $query = $this->getAdapter()->select();
        $query->from($this->_name, array('id'));
        $query->where('mpn like ?', $mpn . '%');
        $result = $this->getAdapter()->fetchCol($query);
        return $result;
    }

    /**
     * Get search result
     * @param string $phrase
     *      phrase
     * @param integer $languageId
     *      language IDs
     * @param array $productIds
     *      product IDs
     * @param array $manufacturerIds
     *      manufacturer IDs
     * @param array $categoryIds
     *      category IDs 
     * @param integer $page
     *      current page 
     * @param integer $rowCount
     *      per page
     */
    public function getSearchResult($phrase, $languageId, $productIds, $manufacturerIds, $categoryIds, $order, $direction, $page, $rowCount)
    {
        $query = $this->getAdapter()->select();
        $query->from(array('p' => $this->_name), array('p.id', 'p.mpn', 'p.name as name', 'p.thumb_pic_url', 'p.price'));
        $query->join(array('c' => 'category'), 'c.id = p.category_id', 'c.id as category_id');
        $query->join(array('ct' => 'category_translation'), 'c.id = ct.category_id', 'ct.name AS category_name');
        $query->join(array('m' => 'manufacturer'), 'm.id = p.manufacturer_id', 'm.name AS manufacturer_name');
        $query->joinLeft(array('pt' => 'product_translation'), 'p.id = pt.product_id', array('pt.short_summary_description'));
        $query->where('pt.language_id IS NULL OR pt.language_id = ?', $languageId);
        $query->where('ct.language_id = ?', $languageId);
        $query->limitPage($page, $rowCount);
        if ($productIds) {
            $query->where('p.id IN (' . implode(',', $productIds) . ')');
        }
        if ($manufacturerIds) {
            $query->where('p.manufacturer_id IN (' . implode(',', $manufacturerIds) . ')');
        }
        if ($categoryIds) {
            $query->where('p.category_id IN (' . implode(',', $categoryIds) . ')');
        }
        if ($phrase) {
            $query->where('p.name like ?', '%' . $phrase . '%');
        }
        $query->order($order . ' ' . $direction);
        $result = $this->getAdapter()->fetchAll($query, null, Zend_Db::FETCH_OBJ);
        // read data about products from result
        foreach ($result as $row) {
            $row->short_summary_description = substr($row->short_summary_description, strpos($row->short_summary_description, ',') + 2);
            $row->category_name = mb_strtoupper(mb_substr($row->category_name, 0, 1, 'utf8'), 'utf8') . mb_substr($row->category_name, 1, 255, 'utf8');
        }
        return $result;
    }

    /**
     * Get size of search result
     * @param string $phrase
     *      phrase
     * @param integer $languageId
     *      language IDs
     * @param array $productIds
     *      product IDs
     * @param array $manufacturerIds
     *      manufacturer IDs
     * @param array $categoryIds
     *      category IDs 
     */
    public function getSearchResultCount($phrase, $languageId, $productIds, $manufacturerIds, $categoryIds)
    {
        $query = $this->getAdapter()->select();
        $query->from(array('p' => $this->_name), new Zend_Db_Expr('count(*)'));
        $query->join(array('c' => 'category'), 'c.id = p.category_id', '');
        $query->join(array('ct' => 'category_translation'), 'c.id = ct.category_id', '');
        $query->join(array('m' => 'manufacturer'), 'm.id = p.manufacturer_id', '');
        $query->joinLeft(array('pt' => 'product_translation'), 'p.id = pt.product_id', '');
        $query->where('pt.language_id IS NULL OR pt.language_id = ?', $languageId);
        $query->where('ct.language_id = ?', $languageId);
        if ($productIds) {
            $query->where('p.id IN (' . implode(',', $productIds) . ')');
        }
        if ($manufacturerIds) {
            $query->where('p.manufacturer_id IN (' . implode(',', $manufacturerIds) . ')');
        }
        if ($categoryIds) {
            $query->where('p.category_id IN (' . implode(',', $categoryIds) . ')');
        }
        if ($phrase) {
            $query->where('p.name like ?', '%' . $phrase . '%');
        }
        $result = $this->getAdapter()->fetchOne($query);
        return $result;
    }

    /**
     * Get products by IDs
     * @param array $ids 
     * @return array
     */
    public function getByIds($ids, $languageId)
    {
        $entries = array();
        if (!$ids) {
            return $entries;
        }
        $query = $this->getAdapter()->select();
        $query->from(array('p' => $this->_name), array('p.id', 'p.mpn', 'p.name as name', 'p.thumb_pic_url', 'p.price'));
        $query->join(array('m' => 'manufacturer'), 'm.id = p.manufacturer_id', 'm.name AS manufacturer_name');
        $query->join(array('c' => 'category'), 'c.id = p.category_id', 'c.id as category_id');
        $query->join(array('ct' => 'category_translation'), 'c.id = ct.category_id', 'ct.name AS category_name');
        $query->where('p.id IN (' . implode(',', $ids) . ')');
        $query->where('ct.language_id = ?', $languageId);
        $result = $this->getAdapter()->fetchAll($query, null, Zend_Db::FETCH_OBJ);
        // read data about products from result
        foreach ($result as $row) {
            $row->category_name = mb_strtoupper(mb_substr($row->category_name, 0, 1, 'utf8'), 'utf8') . mb_substr($row->category_name, 1, 255, 'utf8');
        }
        return $result;
    }

}
